﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace MO
{
    public class SqlDbOperation : IDbOperation
    {
        string connstr;
        static Dictionary<Type, SqlDbType> typeDict = new Dictionary<Type, SqlDbType>();
        static SqlDbOperation()
        {
            typeDict[typeof(int)] = SqlDbType.Decimal;
            typeDict[typeof(string)] = SqlDbType.VarChar;
            typeDict[typeof(DateTime)] = SqlDbType.DateTime;
        }
        public SqlDbOperation(string connstr)
        {
            this.connstr = connstr;

        }
        public void Dispose()
        {
        }
        public int BatchInsert(DataTable table)
        {
            using (SqlBulkCopy sbc = new SqlBulkCopy(connstr, SqlBulkCopyOptions.UseInternalTransaction))
            {
                sbc.BulkCopyTimeout = 5000;
                sbc.NotifyAfter = table.Rows.Count;
                sbc.DestinationTableName = table.TableName;
                sbc.WriteToServer(table);
            }
            return table.Rows.Count;
        }
        public int BatchUpdate(DataTable table, List<string> keys)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                conn.Open();
                DataColumn[] cols = table.Columns.Cast<DataColumn>().ToArray();
                string[] colds = cols.Where(t => !keys.Contains(t.ColumnName)).Select(t => t.ColumnName + " = @" + t.ColumnName).ToArray();
                string sql = string.Format("update {0} set {1} where {2}", table.TableName, string.Join(" , ", colds), string.Join(" , ", keys.Select(t => t + " = @" + t).ToArray()));
                StringBuilder sb = new StringBuilder();
                foreach (DataRow row in table.Rows)
                {
                    string str = sql;
                    foreach (DataColumn col in cols)
                    {
                        if (col.DataType == typeof(int))
                            str = str.Replace("@" + col.ColumnName, row[col].ToString());
                        else
                            str = str.Replace("@" + col.ColumnName, "'" + row[col].ToString() + "'");
                    }
                    sb.AppendLine(str + ";");
                }
                command.CommandText = sb.ToString();
                return command.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 执行SQL语句。
        /// </summary>
        /// <param name="cmdText">SQL查询语句，包含零个或多个格式项。</param>
        /// <param name="args">包含零个或多个要格式化的对象的 Object 数组。</param>
        /// <returns>返回影响的行数。</returns>
        public int ExecuteQuery(string cmdText, params object[] args)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                return Format(cmdText, conn, args).ExecuteNonQuery();
            }

        }
        /// <summary>
        /// 查出某项数据。
        /// </summary>
        /// <param name="cmdText">SQL查询语句，包含零个或多个格式项。</param>
        /// <param name="args">包含零个或多个要格式化的对象的 Object 数组。</param>
        /// <returns>返回第0行第0项数据。</returns>
        public object ExecuteObject(string cmdText, params object[] args)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                return Format(cmdText, conn, args).ExecuteScalar();
            }

        }
        /// <summary>
        /// 查询数据。
        /// </summary>
        /// <param name="cmdText">SQL查询语句，包含零个或多个格式项。</param>
        /// <param name="args">包含零个或多个要格式化的对象的 Object 数组。</param>
        /// <returns>返回DataReader。</returns>
        public DbDataReader ExecuteReader(string cmdText, params object[] args)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                return Format(cmdText, conn, args).ExecuteReader();
            }
        }
        /// <summary>
        /// 查询数据。
        /// </summary>
        /// <param name="cmdText">SQL查询语句，包含零个或多个格式项。</param>
        /// <param name="args">包含零个或多个要格式化的对象的 Object 数组。</param>
        /// <returns>返回DataTable。</returns>
        public DataTable ExecuteDataTable(string cmdText, params object[] args)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                DataTable result = new DataTable();
                SqlDataAdapter oda = new SqlDataAdapter();
                oda.SelectCommand = Format(cmdText, conn, args);
                oda.Fill(result);
                return result;
            }

        }
        /// <summary>
        /// 生成插入语句。
        /// </summary>
        /// <param name="table">表名。</param>
        /// <param name="columns">插入的字段。</param>
        /// <returns>插入语句字符串。</returns>
        public int Insert(string table, Dictionary<string, object> columns)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                List<string> numbers = new List<string>();
                for (int i = 0; i < columns.Count; i++)
                    numbers.Add(string.Concat("{", i, "}"));
                var command = Format(string.Format("insert into {0} ({1}) values ({2})", table, string.Join(",", columns.Keys.ToArray()), string.Join(",", numbers.ToArray())),
                    conn,
                    columns.Values.ToArray());
                return command.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 生成更新语句。
        /// </summary>
        /// <param name="table">表名。</param>
        /// <param name="columns">更新和条件的数据，先添加更新数据，加完后，添加"\r"进行分隔，然后添加条件数据。</param>
        /// <returns>更新语句字符串。</returns>
        public int Update(string table, Dictionary<string, object> columns)
        {

            List<string> contents = new List<string>();
            foreach (string key in columns.Keys)
            {
                if (key == "\r")
                    break;
                contents.Add(key);
            }
            columns.Remove("\r");
            List<string> conditions = new List<string>();
            foreach (string key in columns.Keys)
            {
                if (!contents.Contains(key))
                    conditions.Add(key);
            }
            for (int i = 0; i < columns.Count; i++)
            {
                if (i < contents.Count)
                    contents[i] += string.Concat("={", i, "}");
                else
                    conditions[i - contents.Count] += string.Concat("={", i, "}");
            }
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                var command = Format(string.Format("update {0} set {1} where {2}", table, string.Join(",", contents.ToArray()), string.Join(" and ", conditions.ToArray())),
                    conn,
                    columns.Values.ToArray());
                return command.ExecuteNonQuery();
            }
        }
        SqlCommand Format(string cmdText, SqlConnection conn, params object[] args)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            SqlCommand command = new SqlCommand();
            command.Connection = conn;
            List<string> constants = new List<string>();
            // 调整参数，以供拼凑生成 SQL 语句。
            for (int i = 0; i < args.Length; i++)
            {
                object parameter = args[i];
                // 参数是数字类型时。
                if (parameter is byte || parameter is short || parameter is ushort || parameter is int || parameter is uint || parameter is long || parameter is ulong || parameter is float || parameter is double || parameter is decimal)
                {
                    command.Parameters.Add(CreateParameter("@p" + i.ToString("d3"), System.Data.DbType.Double, parameter));
                    continue;
                }
                if (parameter is bool)
                {
                    command.Parameters.Add(CreateParameter("@p" + i.ToString("d3"), System.Data.DbType.Boolean, (bool)parameter ? 1 : 0));
                    continue;
                }
                if (parameter is string)
                {
                    command.Parameters.Add(CreateParameter("@p" + i.ToString("d3"), System.Data.DbType.String, parameter));
                    continue;
                }
                if (parameter is byte[])
                {
                    command.Parameters.Add(CreateParameter("@p" + i.ToString("d3"), System.Data.DbType.Binary, parameter));
                    continue;
                }
                if (parameter is DateTime)
                {
                    command.Parameters.Add(CreateParameter("@p" + i.ToString("d3"), System.Data.DbType.DateTime, parameter));
                    continue;
                }
                // 参数是空值类型。
                if (parameter == null || parameter is DBNull)
                {
                    command.Parameters.Add(CreateParameter("@p" + i.ToString("d3"), System.Data.DbType.String, DBNull.Value));
                    continue;
                }
                throw new NotSupportedException();
            }
            command.CommandText = string.Format(cmdText, command.Parameters.Cast<IDataParameter>().Select(t => t.ParameterName).ToArray());
            return command;
        }
        SqlParameter CreateParameter(string name, DbType dbType, object value)
        {
            return new SqlParameter(name, value) { DbType = dbType };
        }


    }
}
